package com.moleq.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.moleq.db.DBError;
import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.dialog.MessageBox;
import com.moleq.dialog.MessageDialog;
import com.moleq.dialog.MessageDialog.OnClickMessageListener;
import com.moleq.models.FunctionPanel;

public class FunctionPanelService {
	private DatabaseHelper DBHelper;
	private MessageDialog mMessageDialog;
	Context context;
	
	public FunctionPanelService(){
	}
	
	public FunctionPanelService(Context context){
		this.context = context;
		this.DBHelper = new DatabaseHelper(context);
	}
	
	public List<FunctionPanel> findAvailableFuncations(){
		List<FunctionPanel> obj = new ArrayList<FunctionPanel>();
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select a.*,b.ff_name from "+ DatabaseSchema.FunctionPanel.SCHEMA_NAME +" a left join "+ DatabaseSchema.FreeFunction.SCHEMA_NAME +" b on tp_func_key =  ff_id order by tp_order";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return obj;
		}
		while (cursor.moveToNext()) {
			int tp_no = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_NO));
			int tp_order = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_ORDER));
			int tp_func_key = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_FUNC_KEY));
			String tp_label_file = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_LABEL_FILE));
			String tp_appl_fl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_APPL_FL));
			String tp_parameter1 = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER1));
			int tp_parameter2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER2));
			String create_datetime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.CREATE_DATETIME));
			String update_datetime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.UPDATE_DATETIME));
			double tp_parameter3 = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER3));
			String func_name = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FreeFunction.Name));
			String tp_label_file2 = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_LABEL_FILE2));
			
			obj.add(new FunctionPanel(tp_no, tp_order, tp_func_key, tp_label_file, tp_appl_fl, 
					tp_parameter1, tp_parameter2, create_datetime, update_datetime, tp_parameter3, tp_label_file2));;
		}
		cursor.close();
		return obj;
	}
	
	public List<FunctionPanel> getAllFunctionPanel(){
		List<FunctionPanel> obj = new ArrayList<FunctionPanel>();
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select * from " + DatabaseSchema.FunctionPanel.SCHEMA_NAME + " order by tp_order";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return obj;
		}
		while (cursor.moveToNext()) {
			int tp_no = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_NO));
			int tp_order = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_ORDER));
			int tp_func_key = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_FUNC_KEY));
			String tp_label_file = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_LABEL_FILE));
			String tp_appl_fl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_APPL_FL));
			String tp_parameter1 = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER1));
			int tp_parameter2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER2));
			String create_datetime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.CREATE_DATETIME));
			String update_datetime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.UPDATE_DATETIME));
			double tp_parameter3 = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER3));
			String tp_label_file2 = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_LABEL_FILE2));
			
			obj.add(new FunctionPanel(tp_no, tp_order, tp_func_key, tp_label_file, tp_appl_fl, 
					tp_parameter1, tp_parameter2, create_datetime, update_datetime, tp_parameter3, tp_label_file2));
		}
		cursor.close();
		return obj;
	}
	
//	public List<FunctionPanel> getActiveFunctionPanel(){
//		List<FunctionPanel> obj = new ArrayList<FunctionPanel>();
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
//		String sql = "select * from " + DatabaseSchema.FunctionPanel.SCHEMA_NAME + " where " + DatabaseSchema.FunctionPanel.TP_APPL_FL + "= '1' order by tp_order" ;
//		Cursor cursor = db.rawQuery(sql, null);
//		if (cursor == null) {
//			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
//					mMessageDialog.ICON_ERROR);
//			DBError.error = "";
//			return obj;
//		}
//		while (cursor.moveToNext()) {
//			int tp_no = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_NO));
//			int tp_order = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_ORDER));
//			int tp_func_key = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_FUNC_KEY));
//			String tp_label_file = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_LABEL_FILE));
//			String tp_appl_fl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_APPL_FL));
//			String tp_parameter1 = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER1));
//			int tp_parameter2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER2));
//			double tp_parameter3 = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER3));
//			String tp_label_file2 = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_LABEL_FILE2));
//			obj.add(new FunctionPanel(tp_no, tp_order, tp_func_key, 
//					tp_label_file, tp_appl_fl, tp_parameter1, tp_parameter2, tp_parameter3, tp_label_file2));
//		}
//		cursor.close();
//		return obj;
//	}
	
	public FunctionPanel Find(){
		FunctionPanel obj = null;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select * from " + DatabaseSchema.FunctionPanel.SCHEMA_NAME;
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return obj;
		}
		while (cursor.moveToNext()) {
			int tp_no = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_NO));
			int tp_order = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_ORDER));
			int tp_func_key = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_FUNC_KEY));
			String tp_label_file = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_LABEL_FILE));
			String tp_appl_fl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_APPL_FL));
			String tp_parameter1 = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER1));
			int tp_parameter2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER2));
			String create_datetime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.CREATE_DATETIME));
			String update_datetime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.UPDATE_DATETIME));
			double tp_parameter3 = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER3));
			String tp_label_file2 = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_LABEL_FILE2));
			
			obj = new FunctionPanel(tp_no, tp_order, tp_func_key, tp_label_file, tp_appl_fl, 
					tp_parameter1, tp_parameter2, create_datetime, update_datetime, tp_parameter3, tp_label_file2);
		}
		cursor.close();
		return obj;
	}
	
	public FunctionPanel FindFunctionPanelByNo(int no){
		FunctionPanel obj = null;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select * from " + DatabaseSchema.FunctionPanel.SCHEMA_NAME + " where tp_no = " + no;
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return obj;
		}
		while (cursor.moveToNext()) {
			int tp_no = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_NO));
			int tp_order = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_ORDER));
			int tp_func_key = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_FUNC_KEY));
			String tp_label_file = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_LABEL_FILE));
			String tp_appl_fl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_APPL_FL));
			String tp_parameter1 = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER1));
			int tp_parameter2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER2));
			String create_datetime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.CREATE_DATETIME));
			String update_datetime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.UPDATE_DATETIME));
			double tp_parameter3 = cursor.getDouble(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_PARAMETER3));
			String tp_label_file2 = cursor.getString(cursor.getColumnIndex(DatabaseSchema.FunctionPanel.TP_LABEL_FILE2));
			
			obj = new FunctionPanel(tp_no, tp_order, tp_func_key, tp_label_file, tp_appl_fl, 
					tp_parameter1, tp_parameter2, create_datetime, update_datetime, tp_parameter3, tp_label_file2);
		}
		cursor.close();
		return obj;
	}
	
	public boolean updateFuncionPanel(FunctionPanel obj){
		boolean check = true;
		int order,func_key,parameter2;
		String sql,errorString,label_file,appl_fl,parameter1,label_file2;
		double parameter3;
		order = obj.getTp_order();
		func_key = obj.getTp_func_key();
		label_file = obj.getTp_label_file();
		appl_fl = obj.getTp_appl_fl();
		parameter1 = obj.getTp_parameter1();
		parameter2 = obj.getTp_parameter2();
		parameter3 = obj.getTp_parameter3();
		label_file2 = obj.getTp_label_file2();
		
		sql = "UPDATE "+ DatabaseSchema.FunctionPanel.SCHEMA_NAME + " set ";
		sql += "tp_order = "+ order +",tp_func_key = "+ func_key +"," +
				"tp_label_file = '"+ label_file +"',tp_appl_fl = '"+ appl_fl +"'," +
				"tp_parameter1 = '"+ parameter1 +"',tp_parameter2 = "+ parameter2 +",tp_parameter3 = "+ parameter3 +
				",update_datetime = strftime('%Y%m%d%H%M%S','now'),tp_label_file2 = '"+ label_file2+"' where tp_no = " + obj.getTp_no();
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean insertFuncionPanel(FunctionPanel obj){
		boolean check = true;
		int order,func_key,parameter2;
		String sql,errorString,label_file,appl_fl,parameter1,label_file2;
		double parameter3;
		order = obj.getTp_order();
		func_key = obj.getTp_func_key();
		label_file = obj.getTp_label_file();
		appl_fl = obj.getTp_appl_fl();
		parameter1 = obj.getTp_parameter1();
		parameter2 = obj.getTp_parameter2();
		parameter3 = obj.getTp_parameter3();
		label_file2 = obj.getTp_label_file2();
		
		sql = "Insert into "+ DatabaseSchema.FunctionPanel.SCHEMA_NAME + " values ";
		sql += "(null,"+ order +","+ func_key +",'"+ label_file +"','"+ appl_fl +"'," +
				"'"+ parameter1 +"',"+ parameter2 +",strftime('%Y%m%d%H%M%S','now')," +
				"strftime('%Y%m%d%H%M%S','now'),"+ parameter3+",'"+ label_file2+"')";
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean UpdateOrInsertFuncPanel(FunctionPanel obj){
		if (CheckFuncNoInList(obj.getTp_no())) {
			return this.updateFuncionPanel(obj);
		}else {
			return this.insertFuncionPanel(obj);
		}
	}
	
	public boolean CheckFuncNoInList(int no){
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select tp_no from t_function_panel where tp_no = " + no, null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return false;
		}
		if (cursor.moveToNext()) {
			cursor.close();
			return true;
		}
		cursor.close();
		return false;
	}

	public boolean deleteFunctionPanelByNo(int no){
		boolean check = true;
		String sql,errorString;
		sql = "delete from "+ DatabaseSchema.FunctionPanel.SCHEMA_NAME + " where tp_no = " + no;
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean updateFunctionPanelOrder(int order,int no){
		boolean check = true;
		String sql,errorString;
		sql = "UPDATE " + DatabaseSchema.FunctionPanel.SCHEMA_NAME + " set tp_order = " + order+
				",update_datetime = strftime('%Y%m%d%H%M%S','now') where tp_no = " + no;
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
//	public boolean updateFuncPanelLabel(int no,String label,boolean appr_fl,String p1,int p2){
//		boolean check = true;
//		String sql,errorString,appr;
//		appr = appr_fl?"1":"0";
//		sql = "UPDATE " + DatabaseSchema.FunctionPanel.SCHEMA_NAME + " set tp_label_file = '" + label +"',tp_appl_fl = '"+ appr +"'," +
//				"tp_parameter1 = '"+ p1 +"',tp_parameter2 = "+ p2 +",update_datetime = strftime('%Y%m%d%H%M%S','now') where tp_no = " + no;
//		errorString = DBHelper.execSQL(sql);
//		if (errorString.length() > 0) {
//			check = false;
//		}
//		return check;
//	}
	
	public boolean postUpdateSetFunctionPanelOrder(int newOrder,int oldOrder,int no){
		boolean check = true;
		String sql,errorString;
		if (newOrder < oldOrder) {
			sql = "UPDATE "+ DatabaseSchema.FunctionPanel.SCHEMA_NAME +" set tp_order = tp_order + 1," +
					"update_datetime = strftime('%Y%m%d%H%M%S','now') where tp_no <> " + no + 
					" and tp_order >= " + newOrder + " and tp_order < " + oldOrder;
		}else {
			sql = "UPDATE "+ DatabaseSchema.FunctionPanel.SCHEMA_NAME +" set tp_order = tp_order - 1," +
					"update_datetime = strftime('%Y%m%d%H%M%S','now') where tp_no <> " + no + 
					" and tp_order <= " + newOrder + " and tp_order > " + oldOrder;
		}
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean postDeleteSetFunctionPanelOrder(int order){
		boolean check = true;
		String sql,errorString;
		sql = "UPDATE "+ DatabaseSchema.FunctionPanel.SCHEMA_NAME +" set tp_order = tp_order - 1," +
				"update_datetime = strftime('%Y%m%d%H%M%S','now')" +
				" where tp_order > " + order;
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public String getFuncNameByFuncKey(int func_key){
		String funcName = "";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select ff_name from t_free_functions where ff_id = " + func_key, null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return funcName;
		}
		if (cursor.moveToNext()) {
			funcName = cursor.getString(0);
		}
		cursor.close();
		return funcName;
	}
	
	public int getMaxPanelOrder(){
		int order = 0;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select Max(tp_order) from " + DatabaseSchema.FunctionPanel.SCHEMA_NAME, null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return order;
		}
		if (cursor.moveToNext()) {
			order = cursor.getInt(0);
		}
		cursor.close();
		return order;
	}
	
	public int getMaxPanelNo(){
		int order = 0;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select Max(tp_no) from " + DatabaseSchema.FunctionPanel.SCHEMA_NAME, null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return order;
		}
		if (cursor.moveToNext()) {
			order = cursor.getInt(0);
		}
		cursor.close();
		return order;
	}
	
	public int getSignOutCount(){
		int order = 0;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select count(tp_func_key) from " + DatabaseSchema.FunctionPanel.SCHEMA_NAME + " where tp_func_key = 36", null);
		if (cursor == null) {
			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
					mMessageDialog.ICON_ERROR);
			DBError.error = "";
			return order;
		}
		if (cursor.moveToNext()) {
			order = cursor.getInt(0);
		}
		cursor.close();
		return order;
	}
	
	public void ShowMessage(String text, int type, int icon) {
		mMessageDialog = new MessageDialog(context);
		mMessageDialog.setStyle(type);
		mMessageDialog.setMessageIcon(icon);
		mMessageDialog.setMessage(text);
		mMessageDialog.setOnClickMessageListener(onClickMessageListener);
		mMessageDialog.show();
	}

	private OnClickMessageListener onClickMessageListener = new OnClickMessageListener() {

		@Override
		public void onClickYes() {
			// TODO Auto-generated method stub

		}

		@Override
		public void onClickNo() {
			// TODO Auto-generated method stub

		}

		@Override
		public void onClickOK() {
			// TODO Auto-generated method stub
			mMessageDialog.dismiss();
		}
	};
}
